using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace FrameWorkSystem.BusinessLayer.DataLayer
{
	/// <summary>
	/// Data access layer class for Sys_last_code_id
	/// </summary>
	public class Sys_last_code_idSql : DataLayerBase 
	{

        #region Constructor

		/// <summary>
		/// Class constructor
		/// </summary>
		public Sys_last_code_idSql()
		{
			// Nothing for now.
		}

        #endregion

        #region Public Methods

        /// <summary>
        /// insert new row in the table
        /// </summary>
		/// <param name="businessObject">business object</param>
		/// <returns>true of successfully insert</returns>
		public bool Insert(Sys_last_code_id businessObject)
		{
			SqlCommand	sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "INSERT [sys_last_code_id]([table_name], [code_id],[code_serial], [length], [prefix], [suffix], [ghi_chu]) VALUES( @table_name, @code_id, @code_serial, @length, @prefix, @suffix, @ghi_chu)";
			sqlCommand.CommandType = CommandType.Text;

			// Use connection object of base class
			sqlCommand.Connection = MainConnection;

			try
			{
                
				sqlCommand.Parameters.Add(new SqlParameter("@table_name", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Table_name));
				sqlCommand.Parameters.Add(new SqlParameter("@code_id", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Code_id));
				sqlCommand.Parameters.Add(new SqlParameter("@code_serial", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Code_serial));
				sqlCommand.Parameters.Add(new SqlParameter("@length", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Length));
				sqlCommand.Parameters.Add(new SqlParameter("@prefix", SqlDbType.NVarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Prefix));
				sqlCommand.Parameters.Add(new SqlParameter("@suffix", SqlDbType.NVarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Suffix));
				sqlCommand.Parameters.Add(new SqlParameter("@ghi_chu", SqlDbType.NVarChar, 200, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Ghi_chu));

								
				MainConnection.Open();
				
				sqlCommand.ExecuteNonQuery();
                
				return true;
			}
			catch
			{				
				return false;// Exception("Sys_last_code_id::Insert::Error occured.", ex);
			}
			finally
			{			
				MainConnection.Close();
				sqlCommand.Dispose();
			}
		}

         /// <summary>
        /// update row in the table
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <returns>true for successfully updated</returns>
        public bool Update(Sys_last_code_id businessObject)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "UPDATE [sys_last_code_id] SET	[table_name] = @table_name,[code_id] = @code_id WHERE [table_name] = @table_name";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
				sqlCommand.Parameters.Add(new SqlParameter("@table_name", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Table_name));
				sqlCommand.Parameters.Add(new SqlParameter("@code_id", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Code_id));
                //sqlCommand.Parameters.Add(new SqlParameter("@code_serial", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Code_serial));
                //sqlCommand.Parameters.Add(new SqlParameter("@length", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Length));
                //sqlCommand.Parameters.Add(new SqlParameter("@prefix", SqlDbType.NVarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Prefix));
                //sqlCommand.Parameters.Add(new SqlParameter("@suffix", SqlDbType.NVarChar, 10, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Suffix));
                //sqlCommand.Parameters.Add(new SqlParameter("@ghi_chu", SqlDbType.NVarChar, 200, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Ghi_chu));

                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch 
            {
                return false;// Exception("Sys_last_code_id::Update::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        /// <summary>
        /// Select by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>Sys_last_code_id business object</returns>
        public Sys_last_code_id SelectByPrimaryKey(Sys_last_code_idKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT  * FROM [dbo].[sys_last_code_id]	WHERE [table_name] = @table_name";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@table_name", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.Table_name));

                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    Sys_last_code_id businessObject = new Sys_last_code_id();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);

                    return businessObject;
                }
                else
                {
                    return null;
                }
            }
            catch 
            {
                return null;// Exception("Sys_last_code_id::SelectByPrimaryKey::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>list of Sys_last_code_id</returns>
        public List<Sys_last_code_id> SelectAll()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM [sys_last_code_id]";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch 
            {                
                return null;// Exception("Sys_last_code_id::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>list of Sys_last_code_id</returns>
        public List<Sys_last_code_id> SelectByField(string fieldName, object value)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM [sys_last_code_id] WHERE [" + fieldName + "] = N'" + value + "'";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                MainConnection.Open();
                
                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch 
            {
                return null;// Exception("Sys_last_code_id::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>true for successfully deleted</returns>
        public bool Delete(Sys_last_code_idKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "DELETE FROM [sys_last_code_id] WHERE [table_name] = @table_name";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@table_name", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.Table_name));


                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch 
            {
                return false;// Exception("Sys_last_code_id::DeleteByKey::Error occured.", ex);
            }
            finally
            {                
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>true for successfully deleted</returns>
        public bool DeleteByField(string fieldName, object value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "DELETE FROM [sys_last_code_id] WHERE ["+ fieldName  + "] = N'" + value.ToString() + "'";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;

            }
            catch 
            {                
                return false;// Exception("Sys_last_code_id::DeleteByField::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Populate business object from data reader
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <param name="dataReader">data reader</param>
        internal void PopulateBusinessObjectFromReader(Sys_last_code_id businessObject, IDataReader dataReader)
        {


				businessObject.Table_name = dataReader.GetString(dataReader.GetOrdinal(Sys_last_code_id.Sys_last_code_idFields.Table_name.ToString()));

				businessObject.Code_id = dataReader.GetInt32(dataReader.GetOrdinal(Sys_last_code_id.Sys_last_code_idFields.Code_id.ToString()));

				businessObject.Code_serial = dataReader.GetInt32(dataReader.GetOrdinal(Sys_last_code_id.Sys_last_code_idFields.Code_serial.ToString()));

				businessObject.Length = dataReader.GetInt32(dataReader.GetOrdinal(Sys_last_code_id.Sys_last_code_idFields.Length.ToString()));

				businessObject.Prefix = dataReader.GetString(dataReader.GetOrdinal(Sys_last_code_id.Sys_last_code_idFields.Prefix.ToString()));

				businessObject.Suffix = dataReader.GetString(dataReader.GetOrdinal(Sys_last_code_id.Sys_last_code_idFields.Suffix.ToString()));

				businessObject.Ghi_chu = dataReader.GetString(dataReader.GetOrdinal(Sys_last_code_id.Sys_last_code_idFields.Ghi_chu.ToString()));


        }

        /// <summary>
        /// Populate business objects from the data reader
        /// </summary>
        /// <param name="dataReader">data reader</param>
        /// <returns>list of Sys_last_code_id</returns>
        internal List<Sys_last_code_id> PopulateObjectsFromReader(IDataReader dataReader)
        {

            List<Sys_last_code_id> list = new List<Sys_last_code_id>();

            while (dataReader.Read())
            {
                Sys_last_code_id businessObject = new Sys_last_code_id();
                PopulateBusinessObjectFromReader(businessObject, dataReader);
                list.Add(businessObject);
            }
            return list;

        }

        #endregion

	}
}
